package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import net.sf.json.JSONArray;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.config.ConfigUtil;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.ItemCategoryDao;
import com.powerunion.datacollection.model.ItemCategory;
import com.powerunion.datacollection.model.jsonObject.TreeNode;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class ItemCategoryDaoImpl extends JdbcBase implements ItemCategoryDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			ItemCategoryDaoImpl.class);

	public boolean delete(String[] codes) throws DaoException {
		String sql = "delete from dc_item_category where id = ? ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);

		for (int i = 0; i < codes.length; i++) {
			SqlParameterSet spSet = new SqlParameterSet();
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, codes[i]));
			update.addBatchParameter(spSet);
		}
		try {
			update.access();
		} catch (Exception e) {
			logger.error("删除类别信息失败！", e);
			throw new DaoException("删除类别信息失败！");
		}

		int[] effectCount = update.getBatchEffectCounts();
		if (effectCount != null && effectCount.length > 0) {
//			if (logger.isDebugEnabled()) {
//				logger.debug("delete:" + Constants.DELETE + "类别信息"
//						+ Constants.SUCCESS);
//			}
			return true;
		} else {
//			if (logger.isDebugEnabled()) {
//				logger.debug("delete:" + Constants.DELETE + "类别信息"
//						+ Constants.FAIL);
//			}
			return false;
		}
	}

	public String save(ItemCategory ic, int categoryType) throws DaoException {
		String sql = "";
		String resultId = "";
		if (ic.getId() == null || ic.getId().equals("")
				|| ic.getId().indexOf("xnode") >= 0) {// 新增
			String id = String.valueOf(this.getSeq("dc_item_category"));
			ic.setId(id);
			StringBuffer sqlSave = new StringBuffer();
			sqlSave
					.append(
							"insert into dc_item_category (para_seq,category_name,ordr_no,item_type,category_type,id) ")
					.append("values(?,?,?,?,?,?)");
			sql = sqlSave.toString();
		} else {// 修改
			StringBuffer sqlUpdate = new StringBuffer();
			sqlUpdate
					.append(
							"update dc_item_category set para_seq = ?, category_name = ?,ordr_no=?, ")
					.append("item_type=?,category_type=?  where id =?");
			sql = sqlUpdate.toString();
		}

		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		update.addParameter(new SqlParameter(SqlType.VARCHAR, ic.getParaSeq()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, ic
				.getCategoryName()));
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(ic
				.getDisplayOrder())));
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(ic
				.getItemType())));
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
				categoryType)));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, ic.getId()));
		try {
			update.access();
		} catch (Exception e) {
			logger.error("保存类别信息失败！", e);
			throw new DaoException("保存类别信息失败！");

		}

		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {

//			if (logger.isDebugEnabled()) {
//				logger.debug("save:" + Constants.SAVE + "类别信息"
//						+ Constants.SUCCESS);
//			}
			resultId = ic.getId();
			return resultId;
		} else {
//			if (logger.isDebugEnabled()) {
//				logger
//						.debug("save:" + Constants.SAVE + "类别信息"
//								+ Constants.FAIL);
//			}
			return "";
		}

	}

	public ItemCategory getBykey(String id) throws DaoException {
		ItemCategory ic = new ItemCategory();
		StringBuffer sql = new StringBuffer();
		sql
				.append("select id,para_seq,category_name,ordr_no,item_type from dc_item_category where id = ?");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, id));
		try {
			query.access();
		} catch (Exception e) {
			logger.error("查询类别信息失败！", e);
			throw new DaoException("查询类别信息失败！");
		}

		if (query.resultSetAvailable()) {// 检查结果集是否可用
			// 获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				ic.setId(rs.getFieldValueAsString("id"));
				ic.setParaSeq(rs.getFieldValueAsString("para_seq"));
				ic.setCategoryName(rs.getFieldValueAsString("category_name"));
				ic.setDisplayOrder(rs.getFieldValueAsInteger("ordr_no")
						.intValue());
				ic.setItemType(rs.getFieldValueAsInteger("item_type")
						.intValue());
			}
			rs.clearAll();
		}
		return ic;
	}
	
	
	private JSONArray getParaTree(List categoryIds, int categoryType,String userCode,int permissionType,Connection conn) throws Exception {
		logger.info(Constants.INTOMETHOD + "getParaTree");
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;

		StringBuffer sql = new StringBuffer();
		sql.append("select id,para_seq,category_name,ordr_no,item_type,")
		   .append("decode(sign((SELECT COUNT(f2.id) FROM dc_item_category f2 WHERE f2.para_seq = d.id)), 1 , 1 , 0) childNum ")
		   .append(" from dc_item_category d  where para_seq is null and category_type="+categoryType + " order by ordr_no");
	
		try {		
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {
				String id  = rs.getString("id");
				int childNum = rs.getInt("childNum");
				if(categoryIds != null){
					if(categoryIds.contains(id)){
						TreeNode treeNode = new TreeNode();
						
						treeNode.setId(rs.getString("id"));
						treeNode.setText(rs.getString("category_name"));
						treeNode.setType(String.valueOf(rs.getInt("item_type")));
						treeNode.setExpandable(false);
							
				
						if (childNum > 0) { // 父节点
							treeNode.setLeaf(false);
						} else {// 子节点
							treeNode.setLeaf(true);
						}
						treeNodeArray.add(treeNode);
					}
				
				}else{
					TreeNode treeNode = new TreeNode();
					
					treeNode.setId(rs.getString("id"));
					treeNode.setText(rs.getString("category_name"));
					treeNode.setType(String.valueOf(rs.getInt("item_type")));
					treeNode.setExpandable(false);

					if (childNum > 0) { // 父节点
						treeNode.setLeaf(false);
					} else {// 子节点
						treeNode.setLeaf(true);
					}
					treeNodeArray.add(treeNode);
				}
			}
			// 得到JSON数组
			JSONArray jsonarr = JSONArray.fromObject(treeNodeArray);
			logger.debug("===============category tree:" + jsonarr
					+ "========================");
			// 返回JSON数据
			return jsonarr;
		} catch (Exception e) {
			logger.error("getTree() of throws : ", e);
			throw new Exception(Constants.SEARCH + "category tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps);
		}
	}
	
	private JSONArray getChildrenTree(String paraSeq, int categoryType,Connection conn) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select id,para_seq,category_name,ordr_no,item_type, ")
		   .append("decode(sign((SELECT COUNT(f2.id) FROM dc_item_category f2 WHERE f2.para_seq = d.id)), 1 , 1 , 0) childNum ")
		   .append(" from dc_item_category d where para_seq ='"+ paraSeq+"'")
		   .append(" and category_type="+categoryType).append(" order by ordr_no");
		   
		try {			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {

				TreeNode treeNode = new TreeNode();
				treeNode.setId(rs.getString("id"));
				treeNode.setText(rs.getString("category_name"));
				treeNode.setType(String.valueOf(rs.getInt("item_type")));
				treeNode.setExpandable(false);
				int childNum = rs.getInt("childNum");
				if (childNum > 0) { // 父节点
					treeNode.setLeaf(false);
				} else {// 子节点
					treeNode.setLeaf(true);
				}
				treeNodeArray.add(treeNode);
			}
			// 得到JSON数组
			JSONArray jsonarr = JSONArray.fromObject(treeNodeArray);
			logger.debug("===============category tree:" + jsonarr
					+ "========================");
			// 返回JSON数据
			return jsonarr;
		} catch (Exception e) {
			logger.error("getChildrenTree() of throws : ", e);
			throw new Exception(Constants.SEARCH + "category tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps);
		}
	}

	public JSONArray getTree(String paraSeq, int categoryType,String userCode,int permissionType) throws Exception {
		JSONArray treeNodeArray = null;
		Connection conn = null;
		try{
			conn = this.getConnection();
			if (paraSeq == null || paraSeq.equals("null")) {
				String configureAdminName = ConfigUtil.getInstance().getParameter("system.manager.name");
				if(configureAdminName.equals(userCode)){
					treeNodeArray = this.getParaTree(null, categoryType, userCode, permissionType,conn);
				}else{
					List categoryIds = this.getCategoryByRole(userCode, permissionType, categoryType, conn);
					treeNodeArray = this.getParaTree(categoryIds, categoryType, userCode, permissionType,conn);
				}
				
			} else {
				treeNodeArray = this.getChildrenTree(paraSeq, categoryType,conn);
			}
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("getTree throw error!");
		}finally{
			DBUtils.close(conn);
		}
		return treeNodeArray;
		
	}

	private List getCategoryByRole(String userCode,int permissionType,int categoryType,Connection conn) throws DaoException{
		StringBuffer sql = new StringBuffer();
		sql.append("select category_id from dc_category_roles where role_id = ? and category_type = ?")
		   .append("  and permission_type=? ");
		List categoryIds = null;
		try{
			List roleIds = this.getRoleIdByUser(categoryType, permissionType, userCode, conn);
			categoryIds = new ArrayList();
			for(Iterator iter = roleIds.iterator();iter.hasNext();){
				String roleId = (String)iter.next();
				QueryOperator query = new QueryOperator();
				query.setSql(sql.toString());
				query.setPresentConnection(conn);
				query.setUseOnlyConnectionFlag(true);
				query.addParameter(new SqlParameter(SqlType.VARCHAR, roleId));
				query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
						categoryType)));
				query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
						permissionType)));
				query.access();
				if (query.resultSetAvailable()) {// 检查结果集是否可用
					// 获取并处理记录集
					RsDataSet rs = new RsDataSet(query.getSqlResultSet());
					if (rs.rowCount > 0) {
						for(int j=0;j<rs.rowCount;j++){
							categoryIds.add(rs.getFieldValue("category_id"));
							
							rs.next();
						}
						
					}
					rs.clearAll();
				}
				
			}
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("getCategoryByRole throw error!");
		}
		return categoryIds;
	}
	
	private List getRoleIdByCategoryType(int categoryType,int permissionType, Connection conn)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select distinct role_id from dc_category_roles where category_type = ? ")
				.append(" and permission_type = ?");

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.setUseOnlyConnectionFlag(true);
		List roleIds = null;
		try {
			roleIds = new ArrayList();
			query.setPresentConnection(conn);
			query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					categoryType)));
			query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					permissionType)));

			query.access();

			if (query.resultSetAvailable()) {// 检查结果集是否可用
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					for(int i=0;i<rs.rowCount;i++){
						roleIds.add(rs.getFieldValue("role_id"));
						
						rs.next();
					}
					
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("");
		}
		return roleIds;
	}

	private List getDeptcodeByRole(String roleId, Connection conn)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql.append("select user_code from dc_users_roles where role_id = ? ")
				.append(" and user_type = 'department'");

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.setUseOnlyConnectionFlag(true);
		List deptcodes = null;
		try {
			deptcodes = new ArrayList();
			query.setPresentConnection(conn);
			query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					roleId)));

			query.access();

			if (query.resultSetAvailable()) {// 检查结果集是否可用
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					deptcodes.add(rs.getFieldValue("user_code"));
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getDeptcodeByDeptType throw error!");
		}
		return deptcodes;
	}

	private List getWorkGroupByRole(String roleId, Connection conn)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql.append("select user_code from dc_users_roles where role_id = ? ")
				.append(" and user_type = 'group'");

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.setUseOnlyConnectionFlag(true);
		List workGroups = null;
		try {
			workGroups = new ArrayList();
			query.setPresentConnection(conn);
			query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
					roleId)));

			query.access();

			if (query.resultSetAvailable()) {// 检查结果集是否可用
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					workGroups.add(rs.getFieldValue("user_code"));
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getDeptcodeByDeptType throw error!");
		}
		return workGroups;
	}

	private List getRoleIdByUser(int categoryType,int permissionType, String userCode, Connection conn)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
//		sql
//				.append("select id from dc_users_roles where role_id =? and user_type = 'user' and user_code = ?");
		
		sql
		.append("select id from dc_users_roles where role_id =?  and user_code = ?");

		List roleIds = null;
		try {
			roleIds = new ArrayList();
			List rolds = this.getRoleIdByCategoryType(categoryType, permissionType, conn);

			for (Iterator iter = rolds.iterator(); iter.hasNext();) {
				String roleId = (String) iter.next();
				QueryOperator query = new QueryOperator();
				query.setPresentConnection(conn);
				query.setSql(sql.toString());
				query.setUseOnlyConnectionFlag(true);

				query.addParameter(new SqlParameter(SqlType.VARCHAR, roleId));
				query.addParameter(new SqlParameter(SqlType.VARCHAR, userCode));

				query.access();

				if (query.resultSetAvailable()) {// 检查结果集是否可用
					// 获取并处理记录集
					RsDataSet rs = new RsDataSet(query.getSqlResultSet());
					if (rs.rowCount > 0) {
						roleIds.add(roleId);
					} 
					rs.clearAll();
				}else {
					boolean flag = this.iUsercodeInDept(userCode, roleId,
							conn);
					if (flag) {
						roleIds.add(roleId);
					} else {
						flag = this.iUsercodeInWorkGroup(userCode, roleId,
								conn);
						if (flag) {
							roleIds.add(roleId);
						}
					}
				}
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("");
		}
		return roleIds;
	}

	/**
	 * 判断该角色下部门类型中的部门是否存在该用户
	 * 
	 * @param userCode
	 * @param roleId
	 * @param conn
	 * @return
	 * @throws DaoException
	 */
	private boolean iUsercodeInDept(String userCode, String roleId,
			Connection conn) throws DaoException {
		// 得到该角色下的部门类型的id
		List deptCodes = this.getDeptcodeByRole(roleId, conn);
		StringBuffer sql = new StringBuffer();
		sql.append("select user_code from user_dept where fram_seq = ? ")
				.append(" and user_code = ?");

		boolean flag = false;

		try {
			for (Iterator iter = deptCodes.iterator(); iter.hasNext();) {
				String deptCode = (String) iter.next();
				QueryOperator query = new QueryOperator();
				query.setSql(sql.toString());
				query.setUseOnlyConnectionFlag(true);
				query.setPresentConnection(conn);
				query.addParameter(new SqlParameter(SqlType.VARCHAR, deptCode));
				query.addParameter(new SqlParameter(SqlType.VARCHAR, userCode));

				query.access();
				if (query.resultSetAvailable()) {// 检查结果集是否可用
					// 获取并处理记录集
					RsDataSet rs = new RsDataSet(query.getSqlResultSet());
					if (rs.rowCount > 0) {
						flag = true;
					}
					rs.clearAll();
				}

				if (flag) {
					break;
				}
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("");
		}
		return flag;
	}

	/**
	 * 判断该角色下工作组类型中的工作组是否存在该用户
	 * 
	 * @param userCode
	 * @param roleId
	 * @param conn
	 * @return
	 * @throws DaoException
	 */
	private boolean iUsercodeInWorkGroup(String userCode, String roleId,
			Connection conn) throws DaoException {
		// 得到该角色下的部门类型的id
		List workGroups = this.getWorkGroupByRole(roleId, conn);
		StringBuffer sql = new StringBuffer();
		sql.append("select user_code from user_group where grop_code = ? ")
				.append(" and user_code = ?");

		boolean flag = false;

		try {
			for (Iterator iter = workGroups.iterator(); iter.hasNext();) {
				String workGroup = (String) iter.next();
				QueryOperator query = new QueryOperator();
				query.setSql(sql.toString());
				query.setUseOnlyConnectionFlag(true);
				query.setPresentConnection(conn);
				query
						.addParameter(new SqlParameter(SqlType.VARCHAR,
								workGroup));
				query.addParameter(new SqlParameter(SqlType.VARCHAR, userCode));

				query.access();
				if (query.resultSetAvailable()) {// 检查结果集是否可用
					// 获取并处理记录集
					RsDataSet rs = new RsDataSet(query.getSqlResultSet());
					if (rs.rowCount > 0) {
						flag = true;
					}
					rs.clearAll();
				}

				if (flag) {
					break;
				}
			}

		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("");
		}
		return flag;
	}

	private int IsHasChildren(String parentId, Connection conn)
			throws Exception {

		String sql = "";
		int total = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			sql = "select count(*) from dc_item_category where para_seq=? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, parentId);

			rs = ps.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}

		} catch (Exception e) {
			logger.error("IsHasChildren:" + Constants.SEARCH
					+ "targets children" + Constants.FAIL);
			logger.error("IsHasChildren() of throws : ", e);
			throw new Exception("IsHasChildren:" + Constants.SEARCH
					+ "targets children" + Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps);
		}

		return total;
	}

}
